package com.reserv.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.reserv.db.ConnectionProvider;
import com.reserv.db.JDBCManager;
import com.reserv.vo.MemberVO;

public class MemberDAO implements DAO<MemberVO, String> {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	private static MemberDAO instance = new MemberDAO();
	public static MemberDAO getInstance() {
		return instance != null ? instance : new MemberDAO();
	}
	//create member
	@Override
	public void createVO(MemberVO member) {
		String sql = "insert into member("
				+ "uid, passwd, name, zipcode, addr1, addr2, phone, email, question, answer, birth) "
				+ "value(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, member.getUid());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getName());
			pstmt.setString(4, member.getZipcode());
			pstmt.setString(5, member.getAddr1());
			pstmt.setString(6, member.getAddr2());
			pstmt.setString(7, member.getPhone());
			pstmt.setString(8, member.getEmail());
			pstmt.setString(9, member.getQuestion());
			pstmt.setString(10, member.getAnswer());
			pstmt.setTimestamp(11, member.getBirth());

			pstmt.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
	}

	//get member
	@Override
	public MemberVO getVO(String userId) {
		String sql = "SELECT * FROM member WHERE uid=?";
		MemberVO member = null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, userId);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				member = new MemberVO();

				member.setUid(rs.getString(1));
				member.setPasswd(rs.getString(2));
				member.setName(rs.getString(3));
				member.setZipcode(rs.getString(4));
				member.setAddr1(rs.getString(5));
				member.setAddr2(rs.getString(6));
				member.setPhone(rs.getString(7));
				member.setEmail(rs.getString(8));
				member.setQuestion(rs.getString(9));
				member.setAnswer(rs.getString(10));
				member.setRegDate(rs.getTimestamp(11));
				member.setBirth(rs.getTimestamp(12));
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return member;
	}

	//get memberList
	@Override
	public List<MemberVO> getListVOs() {
		String sql = "SELECT * FROM member";
		List<MemberVO> list= null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			if(rs.next()) {
				list = new ArrayList<>();
				do {
					MemberVO member = new MemberVO();

					member.setUid(rs.getString(1));
					member.setPasswd(rs.getString(2));
					member.setName(rs.getString(3));
					member.setZipcode(rs.getString(4));
					member.setAddr1(rs.getString(5));
					member.setAddr2(rs.getString(6));
					member.setPhone(rs.getString(7));
					member.setEmail(rs.getString(8));
					member.setQuestion(rs.getString(9));
					member.setAnswer(rs.getString(10));
					member.setRegDate(rs.getTimestamp(11));
					member.setBirth(rs.getTimestamp(12));

					list.add(member);
				} while(rs.next());
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	//update member
	@Override
	public void updateVO(MemberVO member) {
		String sql = "회원 정보 업데이트 쿼리 추가";
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);
			/*
			 * 플레이스홀드 설정
			 */
			pstmt.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
	}

	//delete member
	@Override
	public void deleteVO(String uid) {
		String sql = "DELETE FROM member WHERE uid=?";
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, uid);
			
			pstmt.executeUpdate();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
	}

	//check uid
	public boolean checkUid(String uid) {
		String sql = "SELECT COUNT(*) FROM member WHERE uid=?";
		boolean isDuplicated = true;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, uid);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				if(rs.getInt(1) == 0) {
					isDuplicated = false;
				}
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return isDuplicated;
	}
}
